package cn.congine.wizarpos.mall.dao.hibernate;

import java.util.List;

import org.hibernate.FlushMode;
import org.hibernate.Query;
import org.springframework.stereotype.Repository;

import cn.congine.wizarpos.mall.dao.MrtTicketInfoDao;
import cn.congine.wizarpos.mall.model.MrtTicketInfo;
import cn.congine.wizarpos.mall.utils.Function;

@Repository("mrtTicketInfoDao")
public class MrtTicketInfoDaoHibernate extends
		GenericDaoHibernate<MrtTicketInfo> implements MrtTicketInfoDao {

	public MrtTicketInfoDaoHibernate() {
		super(MrtTicketInfo.class);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object> getMrtTicketInfoByCardId(String cardId, int pageNo) {
		String sql = "SELECT t.ticket_name,count(*) as num, max(FORMAT(t.balance/100,2)) as balance, min(t.description) as descn,"
				+ " (case when t.valid_period = -1 then '永久有效' else date_format(ti.expriy_time,'%Y-%m-%d') end) as expriy"
				+ " FROM mrt_ticket_def as t,mrt_ticket_info as ti"
				+ " where t.id = ti.ticket_id"
				+ " and ti.card_id = ?"
				+ " group by t.ticket_name";
		Query query = getSession().createSQLQuery(sql);
		query.setParameter(0, cardId);
		query.setMaxResults(10);
		query.setFirstResult(10 * (pageNo-1));
		return query.list();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object> getUsedTicketInfo(String cardId, int pageNo) {
		String sql = "SELECT t.ticket_name, count(*) as num, max(FORMAT(t.balance/100,2)) as balance,"
				+ " min(t.description) as descn, date_format(ti.used_time,'%Y-%m-%d') as usedTime "
				+ " FROM mrt_ticket_def as t,mrt_ticket_info as ti "
				+ "where t.id = ti.ticket_id and ti.valid_flag = 0 and ti.used_time is not null "
				+ "and ti.card_id = ? group by t.ticket_name";
		Query query = getSession().createSQLQuery(sql);
		query.setMaxResults(10);
		query.setFirstResult(10 * (pageNo-1));
		query.setParameter(0, cardId);
		return query.list();
	}

	@Override
	public Object getIdByWxTicketIdAndNoAndOpenid(String cardId, String no,
			String openid) {
		String sql = "SELECT ti.id"
				+ " FROM mrt_ticket_def as t, mrt_ticket_info as ti, mrt_merchant_card as mc"
				+ " where t.id = ti.ticket_id and mc.id = ti.card_id"
				+ " and t.wx_code_id = ?" + " and ti.ticket_no = ?"
				+ " and mc.open_id = ?" + "group by t.ticket_name";
		Query query = getSession().createSQLQuery(sql);
		query.setParameter(0, cardId);
		query.setParameter(1, no);
		query.setParameter(2, openid);
		return query.uniqueResult();
	}

	@Override
	public MrtTicketInfo getByNo(String mid, String ticketNo) {
		Query query = getSession()
				.createQuery(
						"from MrtTicketInfo where mid = :mid and ticketNo = :ticketNo")
				.setParameter("mid", mid).setParameter("ticketNo", ticketNo);
		return (MrtTicketInfo) query.uniqueResult();
	}

	@Override
	public boolean isExist(String ticketDefId, String cardId, String mid) {
		Query query = getSession()
				.createQuery(
						"from MrtTicketInfo where mid = :mid and ticketId = :ticketId and cardId = :cardId")
				.setParameter("mid", mid).setParameter("ticketId", ticketDefId)
				.setParameter("cardId", cardId);
		List obj = query.list();

		if (obj == null || obj.size() == 0) {
			return false;
		}

		return true;
	}

	@Override
	public MrtTicketInfo save(MrtTicketInfo tInfo) {
		String uuid = Function.getUid();
		tInfo.setId(uuid);
		String sql = "insert into mrt_ticket_info (id,ticket_id,ticket_no,card_id,start_time,expriy_time,"
				+ "valid_flag,master_tran_log_id,remark,wx_added,mid,hb_shared) "
				+ "values(?,?,?,?,?,?,?,?,?,?,?,?)";
		this.getSession().setFlushMode(FlushMode.MANUAL);
		Query q = getSession().createSQLQuery(sql);
		q.setString(0, uuid);
		q.setString(1, tInfo.getTicketId());
		q.setString(2, tInfo.getTicketNo());
		q.setString(3, tInfo.getCardId());
		q.setString(4, tInfo.getStartTime());
		q.setString(5, tInfo.getExpriyTime());
		q.setString(6, tInfo.getValidFlag());
		q.setString(7, tInfo.getMasterTranLogId());
		q.setString(8, tInfo.getRemark());
		q.setCharacter(9, tInfo.getWxAdded());
		q.setString(10, tInfo.getMid());
		q.setString(11, tInfo.getHbShared());
		q.executeUpdate();
		return tInfo;
	}

	// 券实例更新FORDRDS
	@Override
	public void update(MrtTicketInfo tInfo) {
		String sql = "UPDATE mrt_ticket_info "
				+ "SET ticket_id = ?, ticket_no = ?, card_id = ?, start_time = ?, expriy_time = ?,"
				+ "valid_flag = ?, cancel_time = ?, used_time = ?, master_tran_log_id = ?, remark = ?,"
				+ "wx_added = ? " + "WHERE id = ? and mid = ? ";
		this.getSession().setFlushMode(FlushMode.MANUAL);
		Query q = getSession().createSQLQuery(sql);
		q.setString(0, tInfo.getTicketId());
		q.setString(1, tInfo.getTicketNo());
		q.setString(2, tInfo.getCardId());
		q.setString(3,
				tInfo.getStartTime() == null ? null : tInfo.getStartTime());
		q.setString(4,
				tInfo.getExpriyTime() == null ? null : tInfo.getExpriyTime());
		q.setString(5, tInfo.getValidFlag());
		q.setString(6,
				tInfo.getCancelTime() == null ? null : tInfo.getCancelTime());
		q.setString(7, tInfo.getUsedTime() == null ? null : tInfo.getUsedTime());
		q.setString(8, tInfo.getMasterTranLogId());
		q.setString(9, tInfo.getRemark());
		q.setCharacter(10, tInfo.getWxAdded());
		q.setString(11, tInfo.getId());
		q.setString(12, tInfo.getMid());
		q.executeUpdate();
	}

	@Override
	public MrtTicketInfo find(String id, String mid) {
		return (MrtTicketInfo) getSession()
				.createQuery(
						"from MrtTicketInfo b where b.mid=:mid and b.id=:id")
				.setString("mid", mid).setString("id", id).uniqueResult();
	}

	@Override
	public MrtTicketInfo getByTicketDefIdAndCardIdAndMid(String ticketDefId,
			String cardId, String mid) {
		Query query = getSession()
				.createQuery(
						"from MrtTicketInfo where mid = :mid and ticketId = :ticketId and cardId = :cardId")
				.setParameter("mid", mid).setParameter("ticketId", ticketDefId)
				.setParameter("cardId", cardId);
		List obj = query.list();

		if (obj != null && !obj.isEmpty()) {
			return (MrtTicketInfo) obj.get(0);
		} else {
			return null;
		}
	}
}
